#!/use/bin/env python
# encoding:utf-8

'''删除数据库不需要的厂商数据和MEID数据,将数据库安装厂商排序后导出为EXCLE'''

import MySQLdb
import xlwt
import os

count = 0
#处理数据
cs = ["NCMC","RIM",'SHARP公司',"中卫莱康","中辰","中锘基雅","丰尚","众鑫百合","优易","优派环宇","佰利","佳斯特","信云","元一时代","全盈通讯","凤腾伟业","创维","华唐","华录","华硕","华立","华立时代","华翼兴达","南极星","博瑞","卡尔电气","卡美欧","吉邦","启东优思","和信","唐为","垦鑫达","基思瑞","夏新","夏朗","大唐","大成","大显","大显泛泰","大诚联合","天津通广","好利通","威盛","威铂通讯","展翼","库麦","康佳","彤霖","微网信通","惠州德赛电子","扬州万事通","振华","振华欧比","掌中宝","斐讯通信","易丰展业","暂无","未知厂商","杭州东信百丰","桑菲","比酷","洪洋伟业","绵阳国虹","美翼景业","美菱","翼鸣时代","芯洲源","英华达","语信时代","赛鸿通讯","锐族","青橙","首信","高通无线","鹏智","齐乐","中维恒泰","丰尚","乐游互联","京瓷","京瓷振华","深圳峰达通"," "]
meid = ['00000000000000','0','null']
try:
    conn = MySQLdb.connect(host="127.0.0.1",user="root",passwd="123456",db="test",port=3306,charset="utf8")
    cur = conn.cursor()

    for line in cs:
        #cur.execute('DELETE FROM sheet1 WHERE changshang = %s'%line )
        count = count+1
        sql = 'DELETE FROM sheet1 WHERE changshang = \'%s\''%line
        cur.execute(sql)
    for a in meid:
        count = count + 1
        sql2 = 'DELETE FROM sheet1 WHERE MEID = \'%s\''%a
        cur.execute(sql2)
    cur.execute('SELECT * FROM Sheet1 ORDER BY changshang DESC;')

    print '一共删除数据',count
    '''将数据库内容读取到表格    '''
    data = cur.fetchall()   #获取所有处理数据
    # print data[0][0] ,len(data), len(data[0])    #显示数据信息
    print '行数为：' ,len(data),'列数为：', len(data[0])    #显示数据信息

    i = 0
    y = 0
    workbook = xlwt.Workbook(encoding='ascii')   #设置表格字符集
    worksheet = workbook.add_sheet('data')  #设置工作簿名称

    # output.write(0,1,'aaa')
    # 利用双重for循环将获取到字符集内容写入到工作簿
    for sj in data:
        for a in data[0]:
            # print i, y
            worksheet.write(i,y,data[i][y])   #将相应内容写入到工作薄
            y = y+1

        i = i + 1
        y = 0   #每次让Y的值为0   即可实现y不断的从0到3

    workbook.save('d:/123.xls')   #将表格内容保存在本地
    print '******表格写入成功*******'
    conn.commit()     #提交数据库处理命令
    print '*******数据库命令提交成功*******data.py***'
except MySQLdb.Error,e:
    print e
    conn.rollback()
finally:
    cur.close()
    conn.close()
